package com.platform.common.util.excel;

import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.platform.common.util.DateUtils;
import com.platform.common.util.ReflectionUtils;

/**
 * 数据导出成excel,response相应输出
 * 
 * @author xieyang
 * 
 */
@SuppressWarnings({ "deprecation", "unchecked", "resource" })
public class ExcelUtil {

	public static Logger logger = Logger.getLogger(ExcelUtil.class);
	private static Workbook wb;

	private static CellStyle titleStyle; // 标题行样式
	private static Font titleFont; // 标题行字体
	private static CellStyle dateStyle; // 日期行样式
	private static Font dateFont; // 日期行字体
	private static CellStyle headStyle; // 表头行样式
	private static Font headFont; // 表头行字体
	private static CellStyle contentStyle; // 内容行样式
	private static Font contentFont; // 内容行字体
	private static final String EXTENSION_XLS = "xls";
	private static final String EXTENSION_XLSX = "xlsx";

	/**
	 * 解析excel并返回内容信息，以sheet作为key
	 * 
	 * @param excelResourceFile
	 * @return
	 * @throws Exception
	 */
	public static List<SheetContent> getExcelInfo(File excelResourceFile) throws Exception {

		if (excelResourceFile == null || (!excelResourceFile.exists())) {
			// 判断文件是否存在
			throw new Exception("file is not exist!");
		} else {
			// 文件路径
			String path = excelResourceFile.getPath();
			Workbook workbook = null;
			if (path.endsWith(EXTENSION_XLS)) {
				// excel2003版本
				workbook = new HSSFWorkbook(new FileInputStream(excelResourceFile));
			} else if (path.endsWith(EXTENSION_XLSX)) {
				// excel2007版本以上
				workbook = new XSSFWorkbook(new FileInputStream(excelResourceFile));
			}

			if (workbook != null) {
				return parseExcel(workbook);
			}

		}
		return null;
	}

	/**
	 * 解析Excel
	 * 
	 * @param workbook
	 * @return
	 * @throws Exception
	 */
	private static List<SheetContent> parseExcel(Workbook workbook) {
		// excel解析结果存储引用
		List<SheetContent> sheetContents = new ArrayList<SheetContent>();
		try {
			// 获取excel中包含的sheet总数
			int sheetNumber = workbook.getNumberOfSheets();
			// 遍历sheet
			for (int i = 0; i < sheetNumber; i++) {
				Sheet sheet = workbook.getSheetAt(i);
				if (sheet == null) {
					// sheet为空时，继续循环
					continue;
				}
				// 单个sheet中行开始编号
				int firstRowNumIndex = sheet.getFirstRowNum();
				// 单个sheet中行结束编号
				int lastRowNumIndex = sheet.getLastRowNum();
				// sheet内容存储对象
				SheetContent sheetContent = new SheetContent();
				sheetContent.setSheetName(sheet.getSheetName());
				// 除表头行以外的存储对象
				Map<Object, List<Object>> sheetContentMap = new HashMap<Object, List<Object>>();

				// 遍历sheet
				for (int j = firstRowNumIndex; j <= lastRowNumIndex; j++) {
					// 获取行对象
					Row row = sheet.getRow(j);
					if (row == null) {
						break;
					}
					List<Object> contentList = new ArrayList<Object>();
					// 遍历单个行的列
					for (int m = row.getFirstCellNum(); m <= row.getLastCellNum(); m++) {
						contentList.add(getCellValue(row.getCell(m), true));
					}
					sheetContentMap.put(row.getRowNum(), contentList);
				}
				// 将单个sheet的内容保存到此map中
				sheetContent.setSheetContentMap(sheetContentMap);
				sheetContents.add(sheetContent);
			}

		} catch (Exception e) {
			logger.error("Parse Excel fail , error: " + e);
		}

		return sheetContents;
	}

	/**
	 * 获取cell中的值
	 * 
	 * @param cell
	 * @param treatAsStr
	 * @return
	 */
	private static String getCellValue(Cell cell, boolean treatAsStr) {
		if (cell == null) {
			return "";
		}

		if (treatAsStr) {
			// 虽然excel中设置的都是文本，但是数字文本还被读错，如“1”取成“1.0”
			// 加上下面这句，临时把它当做文本来读取
			cell.setCellType(Cell.CELL_TYPE_STRING);
		}

		if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
			return String.valueOf(cell.getBooleanCellValue());
		} else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
			return String.valueOf(cell.getNumericCellValue());
		} else {
			return String.valueOf(cell.getStringCellValue());
		}
	}

	/**
	 * excel文件写出
	 * 
	 * @param request
	 * @param response
	 * @param fileName
	 * @param hssfWorkbook
	 */
	public static void writeExcelToResponse(HttpServletRequest request, HttpServletResponse response, String fileName, Workbook workbook) {
		try {
			response.setHeader("content-disposition", "attachment;filename=" + new String(fileName.getBytes(), "ISO8859-1") + ".xlsx");
			response.setContentType("Application/msexcel;charset=utf-8");
			OutputStream fOut = response.getOutputStream();
			workbook.write(fOut);
		} catch (Exception e) {
			logger.error("Write excel to response error, " + e);
		}
	}

	/**
	 * excel文件打包成zip文件
	 * 
	 * @param request
	 * @param response
	 * @param fileName
	 *            zip文件夹名称
	 * @param hssfWorkbook
	 */
	public static void writeExcelToResponseUseZip(HttpServletRequest request, HttpServletResponse response, String fileName, Workbook[] workbooks) {
		try {
			int flag = 1;
			// fileName = URLEncoder.encode(fileName, "UTF-8");
			String zipFileName = new String(fileName.getBytes(), "ISO8859-1") + ".zip";
			response.setContentType("application/zip");
			response.setHeader("content-disposition", "attachment;filename=" + zipFileName);
			ZipOutputStream out = new ZipOutputStream(response.getOutputStream());
			for (Workbook workbook : workbooks) {
				// PS:excel写入到zip时，不能直接从workbook中写入到zip
				String excelFileName = fileName + "_" + flag + ".xlsx";
				File excelFile = new File(excelFileName);
				FileOutputStream fileOutputStream = new FileOutputStream(excelFileName);
				// 先将excel写入到文件中，后续从文件中读一次
				workbook.write(fileOutputStream);
				fileOutputStream.flush();
				fileOutputStream.close();
				ZipEntry entry = new ZipEntry(excelFileName);
				out.putNextEntry(entry);
				// 以下是读文件 操作
				FileInputStream fileInputStream = new FileInputStream(excelFileName);
				byte[] buf = new byte[2048];
				BufferedInputStream origin = new BufferedInputStream(fileInputStream, 2048);
				int len;
				while ((len = origin.read(buf, 0, 2048)) != -1) {
					out.write(buf, 0, len);
				}
				excelFile.deleteOnExit();
				flag++;
			}
			// 写入完成后刷新
			out.finish();
		} catch (Exception e) {
			logger.error("Write excel to response error, " + e);
		}
	}

	/**
	 * @Description: 将Map里的集合对象数据输出Excel数据流
	 * @param setInfo
	 * @throws IOException
	 * @throws IllegalArgumentException
	 * @throws IllegalAccessException
	 */
	public static Workbook export2Excel(ExportExcelDataInfo dataInfo) throws IOException, IllegalArgumentException, IllegalAccessException {
		LinkedHashMap<SheetDesc, List<?>> map = dataInfo.getObjsMap();
		// 初始化excel
		init();
		Set<SheetDesc> sheetDescSet = map.keySet();
		Integer i = 0;
		for (SheetDesc sheetDesc : sheetDescSet) {
			i++;
			// 获取显示值与过滤值之间的关系
			List<FiledDescription> filedDescriptions = sheetDesc.getExcelFiledDescriptions();
			// sheet Name
			String sheetName = sheetDesc.getSheetName();
			Sheet sheet = getSheets(sheetName);

			// 创建sheet中的表头
			createTableTitleRow(sheetDesc, sheet);
			// 日期行
			createTableDateRow(sheetDesc, sheet);
			// 表头
			creatTableHeadRow(sheetDesc, sheet);
			// 设置宽度自适应
			antoChangeWith(sheet, sheetDesc.getExcelFiledDescriptions().size(), filedDescriptions);
			// 待写入数据
			List<?> objects = map.get(sheetDesc);
			int rowNum = 3;
			if (objects != null && objects.size() > 0) {
				for (Object object : objects) {
					if (rowNum > 1000003) {
						break;
					}
					Row hssfRow = sheet.createRow(rowNum);
					int cellNum = 0;
					for (FiledDescription filedDescription : filedDescriptions) {
						Cell hssfCell = hssfRow.createCell(cellNum);
						String fieldName = filedDescription.getFieldName();

						String value = "";
						if (object instanceof Map) {
							Map<String, Object> mapTmp = (Map<String, Object>) object;
							// 如果为null,用空字符串代替,如果为Date类型,格式化为标准格式yyyy-MM-dd
							// HH:mm:ss
							Object obj = mapTmp.get(fieldName);
							value = obj == null ? "" : obj instanceof Date ? DateUtils.formatDatetime((Date) obj) : obj.toString();
						} else {
							// 如果为null,用空字符串代替,如果为Date类型,格式化为标准格式yyyy-MM-dd
							// HH:mm:ss
							Object obj = ReflectionUtils.invokeGetterMethod(object, fieldName);
							value = obj == null ? "" : obj instanceof Date ? DateUtils.formatDatetime((Date) obj) : obj.toString();
						}
						hssfCell.setCellValue(value);
						cellNum++;
					}
					rowNum++;
				}
			}
			if (dataInfo.getB() != null && i == dataInfo.getSheetNum()) {
				// 画图的顶级管理器，一个sheet只能获取一个（一定要注意这点）
				Drawing patriarch = sheet.createDrawingPatriarch();
				// anchor主要用于设置图片的属性
				XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 255, 255, Short.parseShort(dataInfo.getCol1().toString()), dataInfo.getRow1(),
				        Short.parseShort(dataInfo.getCol2().toString()), dataInfo.getRow2());
				anchor.setAnchorType(3);
				// 插入图片
				patriarch.createPicture(anchor, wb.addPicture(dataInfo.getB(), XSSFWorkbook.PICTURE_TYPE_JPEG));
			}
		}
		return wb;
	}

	private static void antoChangeWith(Sheet sheet, int columnNum, List<FiledDescription> descriptions) {
		for (int i = 0; i < columnNum; i++) {
			sheet.autoSizeColumn(i);
		}
	}

	/**
	 * 初始化workBook Excel文件，设置字体及格式
	 */
	private static void init() {
		wb = new SXSSFWorkbook(500);

		titleFont = wb.createFont();
		titleStyle = wb.createCellStyle();
		dateStyle = wb.createCellStyle();
		dateFont = wb.createFont();
		headStyle = wb.createCellStyle();
		headFont = wb.createFont();
		contentStyle = wb.createCellStyle();
		contentFont = wb.createFont();
		initTitleCellStyle();
		initTitleFont();
		initDateCellStyle();
		initDateFont();
		initHeadCellStyle();
		initHeadFont();
		initContentCellStyle();
		initContentFont();
	}

	/**
	 * @Description: 自动调整列宽
	 */
	@SuppressWarnings("unused")
	private static void adjustColumnSize(Sheet[] sheets, int sheetNum, String[] fieldNames) {
		for (int i = 0; i < fieldNames.length + 1; i++) {
			sheets[sheetNum].autoSizeColumn(i, true);
		}
	}

	/**
	 * @Description: 创建标题行(需合并单元格)
	 */
	private static void createTableTitleRow(SheetDesc sheetDesc, Sheet sheet) {
		CellRangeAddress titleRange = new CellRangeAddress(0, 0, 0, sheetDesc.getExcelFiledDescriptions().size() - 1);
		sheet.addMergedRegion(titleRange);
		Row titleRow = sheet.createRow(0);
		titleRow.setHeight((short) 600);
		Cell titleCell = titleRow.createCell(0);
		titleCell.setCellStyle(titleStyle);
		titleCell.setCellValue(sheetDesc.getTitle());
	}

	/**
	 * @Description: 创建日期行(需合并单元格)
	 */
	private static void createTableDateRow(SheetDesc sheetDesc, Sheet sheet) {
		CellRangeAddress dateRange = new CellRangeAddress(1, 1, 0, sheetDesc.getExcelFiledDescriptions().size() - 1);
		sheet.addMergedRegion(dateRange);
		Row dateRow = sheet.createRow(1);
		dateRow.setHeight((short) 350);
		Cell dateCell = dateRow.createCell(0);
		dateCell.setCellStyle(dateStyle);
		dateCell.setCellValue(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date()));
	}

	/**
	 * @Description: 创建表头行(需合并单元格)
	 */
	private static void creatTableHeadRow(SheetDesc sheetDesc, Sheet sheet) {
		// 表头
		Row headRow = sheet.createRow(2);
		headRow.setHeight((short) 350);
		// 序号列
		Cell snCell = headRow.createCell(0);
		snCell.setCellStyle(headStyle);
		// snCell.setCellValue("序号");
		// 列头名称
		for (int num = 0, len = sheetDesc.getExcelFiledDescriptions().size(); num < len; num++) {
			Cell headCell = headRow.createCell(num);
			headCell.setCellStyle(headStyle);
			headCell.setCellValue(sheetDesc.getExcelFiledDescriptions().get(num).getFieldDesc());
		}
	}

	/**
	 * @Description: 创建所有的Sheet
	 */
	private static Sheet getSheets(String sheetName) {
		return wb.createSheet(sheetName);
	}

	/**
	 * @Description: 创建内容行的每一列(附加一列序号)
	 */
	@SuppressWarnings("unused")
	private static Cell[] getCells(Row contentRow, int num) {
		Cell[] cells = new HSSFCell[num + 1];

		for (int i = 0, len = cells.length; i < len; i++) {
			cells[i] = contentRow.createCell(i);
			cells[i].setCellStyle(contentStyle);
		}
		// 设置序号列值，因为出去标题行和日期行，所有-2
		cells[0].setCellValue(contentRow.getRowNum() - 2);

		return cells;
	}

	/**
	 * @Description: 初始化标题行样式
	 */
	private static void initTitleCellStyle() {
		titleStyle.setAlignment(CellStyle.ALIGN_CENTER);
		titleStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		titleStyle.setFont(titleFont);
		titleStyle.setFillBackgroundColor(IndexedColors.SKY_BLUE.index);
	}

	/**
	 * @Description: 初始化日期行样式
	 */
	private static void initDateCellStyle() {
		dateStyle.setAlignment(CellStyle.ALIGN_RIGHT);
		dateStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);

		dateStyle.setFont(dateFont);
		dateStyle.setFillBackgroundColor(IndexedColors.SKY_BLUE.index);
	}

	/**
	 * @Description: 初始化表头行样式
	 */
	private static void initHeadCellStyle() {
		headStyle.setAlignment(CellStyle.ALIGN_CENTER);
		headStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		headStyle.setFont(headFont);
		headStyle.setFillBackgroundColor(IndexedColors.YELLOW.index);
		headStyle.setBorderTop(CellStyle.BORDER_MEDIUM);
		headStyle.setBorderBottom(CellStyle.BORDER_THIN);
		headStyle.setBorderLeft(CellStyle.BORDER_THIN);
		headStyle.setBorderRight(CellStyle.BORDER_THIN);
		headStyle.setTopBorderColor(IndexedColors.BLUE.index);
		headStyle.setBottomBorderColor(IndexedColors.BLUE.index);
		headStyle.setLeftBorderColor(IndexedColors.BLUE.index);
		headStyle.setRightBorderColor(IndexedColors.BLUE.index);
	}

	/**
	 * @Description: 初始化内容行样式
	 */
	private static void initContentCellStyle() {
		contentStyle.setAlignment(CellStyle.ALIGN_CENTER);
		contentStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		contentStyle.setFont(contentFont);
		contentStyle.setBorderTop(CellStyle.BORDER_THIN);
		contentStyle.setBorderBottom(CellStyle.BORDER_THIN);
		contentStyle.setBorderLeft(CellStyle.BORDER_THIN);
		contentStyle.setBorderRight(CellStyle.BORDER_THIN);
		contentStyle.setTopBorderColor(IndexedColors.BLUE.index);
		contentStyle.setBottomBorderColor(IndexedColors.BLUE.index);
		contentStyle.setLeftBorderColor(IndexedColors.BLUE.index);
		contentStyle.setRightBorderColor(IndexedColors.BLUE.index);
		contentStyle.setWrapText(true); // 字段换行
	}

	/**
	 * @Description: 初始化标题行字体
	 */
	private static void initTitleFont() {
		titleFont.setFontName("华文楷体");
		titleFont.setFontHeightInPoints((short) 20);
		titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
		titleFont.setCharSet(Font.DEFAULT_CHARSET);
		titleFont.setColor(IndexedColors.BLUE_GREY.index);
	}

	/**
	 * @Description: 初始化日期行字体
	 */
	private static void initDateFont() {
		dateFont.setFontName("隶书");
		dateFont.setFontHeightInPoints((short) 10);
		dateFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
		dateFont.setCharSet(Font.DEFAULT_CHARSET);
		dateFont.setColor(IndexedColors.BLUE_GREY.index);
	}

	/**
	 * @Description: 初始化表头行字体
	 */
	private static void initHeadFont() {
		headFont.setFontName("宋体");
		headFont.setFontHeightInPoints((short) 10);
		headFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
		headFont.setCharSet(Font.DEFAULT_CHARSET);
		headFont.setColor(IndexedColors.BLUE_GREY.index);
	}

	/**
	 * @Description: 初始化内容行字体
	 */
	private static void initContentFont() {
		contentFont.setFontName("宋体");
		contentFont.setFontHeightInPoints((short) 10);
		contentFont.setBoldweight(Font.BOLDWEIGHT_NORMAL);
		contentFont.setCharSet(Font.DEFAULT_CHARSET);
		contentFont.setColor(IndexedColors.BLUE_GREY.index);
	}

	/**
	 * 生成标题excel单元格数据
	 * 
	 * @Desc:
	 * @date:Nov 2, 2017 3:31:08 PM
	 * @param key
	 *            ：单元格名称,以分号连接
	 * @param value
	 *            ：单元格对应的列字段;以分号连接
	 * @return
	 */
	public static List<FiledDescription> splitTitle(String key, String value) {

		List<FiledDescription> list = new ArrayList<FiledDescription>();

		if (StringUtils.isNotEmpty(key) && StringUtils.isNotEmpty(value)) {
			String[] keysplit = key.split(";");
			String[] valuesplit = value.split(";");
			for (int i = 0; i < keysplit.length; i++) {
				FiledDescription fdesc = new FiledDescription(keysplit[i], valuesplit[i]);
				list.add(fdesc);
			}
		}
		return list;
	}

	/**
	 * 生成标题excel单元格数据
	 * 
	 * @Desc:
	 * @date:Nov 2, 2017 3:31:08 PM
	 * @param key
	 *            ：单元格名称
	 * @param value
	 *            ：单元格对应的列字段
	 * @return
	 */
	public static FiledDescription createTitle(String key, String value) {

		FiledDescription fdesc = new FiledDescription(key, value);
		return fdesc;
	}

	/**
	 * 
	 * @Desc:创建sheet
	 * @date:Nov 17, 2017 2:26:20 PM
	 * @param title
	 *            :标题名称
	 * @param sheetName
	 *            ：sheet名称
	 * @return
	 */
	public static SheetDesc createSheetDesc(String title, String sheetName) {
		// 创建sheet描述
		SheetDesc desc = new SheetDesc();
		// 设置单个sheet的表头名称
		desc.setTitle(title);
		// 设置sheet的名称
		desc.setSheetName(sheetName);
		return desc;
	}

	/**
	 * 生成标题数据
	 * 
	 * @Desc:
	 * @date:Nov 2, 2017 3:30:36 PM
	 * @param title
	 *            :标题名称
	 * @param sheetName
	 *            ：sheet名称
	 * @param descriptions
	 *            ：单元格数据集
	 * @return
	 */
	public static SheetDesc createSheetDesc(String title, String sheetName, List<FiledDescription> descriptions) {
		// 创建sheet描述
		SheetDesc desc = new SheetDesc();
		// 设置单个sheet的表头名称
		desc.setTitle(title);
		// 设置sheet的名称
		desc.setSheetName(sheetName);
		// 列名称与实体属性关系存储到集合中
		desc.setExcelFiledDescriptions(descriptions);
		return desc;
	}

	/**
	 * 生成workbook,
	 * 
	 * @Desc:
	 * @date:Nov 2, 2017 3:29:10 PM
	 * @param sheetDesc
	 *            ：标题数据
	 * @param dataList
	 *            :excel正文数据集
	 * @param isMap
	 *            :isMap 是objsMap中的List<Object>的对象是否是MAP
	 * @return
	 * @throws Exception
	 */
	public static Workbook createWorkbook(SheetDesc sheetDesc, List<?> dataList, boolean isMap) throws Exception {
		// 获取设备信息
		LinkedHashMap<SheetDesc, List<?>> resultMap = new LinkedHashMap<SheetDesc, List<?>>();
		// 构建返回模型
		resultMap.put(sheetDesc, dataList);

		ExportExcelDataInfo dataInfo = new ExportExcelDataInfo();
		dataInfo.setObjsMap(resultMap);
		dataInfo.setMap(isMap);
		Workbook workbook = ExcelUtil.export2Excel(dataInfo);
		return workbook;
	}

	/**
	 * 生成excel数据
	 * 
	 * @Desc:
	 * @date:Nov 17, 2017 2:38:38 PM
	 * @param sheetDesc
	 * @param dataList
	 * @return
	 * @throws Exception
	 */
	public static LinkedHashMap<SheetDesc, List<?>> setSheetData(SheetDesc sheetDesc, List<?> dataList) throws Exception {
		// 获取设备信息
		LinkedHashMap<SheetDesc, List<?>> resultMap = new LinkedHashMap<SheetDesc, List<?>>();
		// 构建返回模型
		resultMap.put(sheetDesc, dataList);
		return resultMap;
	}

	/**
	 * 创建excel导出
	 * 
	 * @Desc:
	 * @date:Nov 17, 2017 2:38:56 PM
	 * @param resultMap
	 * @param isMap
	 * @return
	 * @throws Exception
	 */
	public static Workbook createWorkbook(LinkedHashMap<SheetDesc, List<?>> resultMap, boolean isMap) throws Exception {
		ExportExcelDataInfo dataInfo = new ExportExcelDataInfo();
		dataInfo.setObjsMap(resultMap);
		dataInfo.setMap(isMap);
		Workbook workbook = ExcelUtil.export2Excel(dataInfo);
		return workbook;
	}

}